package db;

import java.math.BigInteger;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.sql.Timestamp;

import org.apache.log4j.Logger;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.Expression;
import org.hibernate.criterion.Restrictions;

import taper.controller.wcs.WCSExcelProcessor;



import db.entity.*;


public class DatabaseManager {
	private static final Logger log = Logger.getLogger(DatabaseManager.class);
//	1. Main Menu
//	SELECT * FROM stationconnection;
	public List<StationConnection> listStationConn(){
		
		List<StationConnection> scList = null;
		EntityWrapper<StationConnection> db = new EntityWrapper<StationConnection>( );
		
	    try {
	    	String sql = "SELECT sc FROM StationConnection sc";
	    	scList = db.query(sql);
	    	
	        db.commit( );
	     } catch ( Exception e ) {
	    	 log.error("listStationConn",e);
	        db.rollback( );
	     }
		return scList;
	}
	
	public List<StationConnection> listStationConnStationName(String stationName){
		
		List<StationConnection> scList = null;
		EntityWrapper<StationConnection> db = new EntityWrapper<StationConnection>( );
		
	    try {
	    	String sql = "SELECT sc FROM StationConnection sc WHERE sc.stationName='"+stationName+"'";
	    	scList = db.query(sql);
	    	
	        db.commit( );
	     } catch ( Exception e ) {
	    	 log.error("listStationConnStationName",e);
	        db.rollback( );
	     }
		return scList;
	}
	
//	2. Live Data Monitor
//	SELECT * FROM stationstatus WHERE stationName = �Station 01� LIMIT 100;
	public List<StationStatus> listStationStatusByStationName(String stationName){
		
		List<StationStatus> sList = null;
		EntityWrapper<StationStatus> db = new EntityWrapper<StationStatus>( );
		Criterion cri =  Restrictions.eq("id.name", stationName);
	    try {
	    	sList = db.query(new StationStatus(),1,"id.dataDate","desc",cri);
	    	
	        db.commit( );
	     } catch ( Exception e ) {
	    	 log.error("listStationStatusByStationName",e);
	        db.rollback( );
	     }
		return sList;
	}
	
//	3. Query Data
//	SELECT * FROM stationstatus WHERE dataDate BETWEEN �from date� AND �to date� AND name = �stationName� ;
	public List<StationStatus> listStationStatusByStationNameDate(String stationName , Date startDate , Date endDate){
		
		List<StationStatus> sList = null;
		EntityWrapper<StationStatus> db = new EntityWrapper<StationStatus>( );
		Criterion cri =  Restrictions.eq("id.name", stationName);
		Criterion cri2 = Restrictions.between("id.dataDate", new Timestamp(startDate.getTime()), new Timestamp(endDate.getTime()));
	    try {
	    	sList = db.query(new StationStatus(),0,"id.dataDate","desc",cri,cri2);
	    	
	        db.commit( );
	     } catch ( Exception e ) {
	    	 log.error("listStationStatusByStationNameDate",e);
	        db.rollback( );
	     }
		return sList;
	}
	
	
	//SELECT * FROM padlife WHERE dataDate BETWEEN �from date� AND �to date� AND  stationName = �stationName�;
	//5. Query SPC
//	/SELECT * FROM padlife WHERE startLife BETWEEN �from date� AND �to date� AND stationName = �station�;
	public List<PadLife> listPadlifeByStationNameDate(String stationName , Date startDate , Date endDate){
		
		List<PadLife> sList = null;
		EntityWrapper<PadLife> db = new EntityWrapper<PadLife>( );
		Criterion cri =  Restrictions.eq("id.stationName", stationName);
		Criterion cri2 = Restrictions.between("id.startLife", new Date(startDate.getTime()), new Date(endDate.getTime()));
	    try {
	    	sList = db.query(new PadLife(),100,"id.startLife","asc",cri,cri2);
	    	
	        db.commit( );
	     } catch ( Exception e ) {
	    	 log.error("listPadlifeByStationNameDate",e);
	        db.rollback( );
	     }
		return sList;
	}
	
	public PadLife listLatestPadLife(String stationName){
		
		PadLife padLife = null;
		EntityWrapper<PadLife> db = new EntityWrapper<PadLife>( );
		
	    try {
	    	String sql = "SELECT pl FROM PadLife pl where id.startLife =(SELECT MAX(id.startLife) from PadLife where id.stationName='" + stationName + "') and stationName='" + stationName + "'";
	    	padLife = db.queryUnique(sql);
	    	
	        db.commit( );
	     } catch ( Exception e ) {
	    	 log.error("listLatestPadLife",e);
	        db.rollback( );
	     }
		return padLife;
		
	}
	
	public List<PadLife> listPadlifeByStationName(String stationName , String orderType ,int limit){
		
		List<PadLife> sList = null;
		EntityWrapper<PadLife> db = new EntityWrapper<PadLife>( );
		Criterion cri =  Restrictions.eq("id.stationName", stationName);
		try {
	    	sList = db.query(new PadLife(),limit,"id.startLife",orderType,cri);
	    	
	        db.commit( );
	     } catch ( Exception e ) {
	    	 log.error("listPadlifeByStationName",e);
	        db.rollback( );
	     }
		return sList;
	}
	
	public Long listPadElapseByPadId(String padID){
		
		Long peList = null;
		EntityWrapper<Long> db = new EntityWrapper<Long>( );
		
	    try {
	    	String sql = "SELECT sum(elapseTimeMillis) FROM PadeLapse pe where pe.id.padID='"+padID+"'";
	    	peList = db.queryUnique(sql);
	    	
	        db.commit( );
	     } catch ( Exception e ) {
	    	 log.error("listPadElapseByPadId",e);
	        db.rollback( );
	     }
		return peList;
	}
	
	public List<PadeLapse> listPadElapseOfPadID(String padID)
	{
		EntityWrapper<PadeLapse> db = new EntityWrapper<PadeLapse>();
		List<PadeLapse> sList = null;
		Criterion cri =  Restrictions.eq("id.padID", padID);
		try {
	    	sList = db.query(new PadeLapse(),0,"id.startTime","asc",cri);
	        db.commit( );
	     } catch ( Exception e ) {
	        db.rollback( );
	     }
		
		return sList;
	}
	
	public List<PadeLapse> listPadElapseByStationNameDate(String stationName, Date startDate , Date endDate){
		
		List<PadeLapse> sList = null;
		EntityWrapper<PadeLapse> db = new EntityWrapper<PadeLapse>( );
		String startDate_str = new Timestamp(startDate.getTime()).toString();
		String endDate_str = new Timestamp(endDate.getTime()).toString();
		String sql = "SELECT pl FROM PadeLapse pl WHERE " +"SUBSTRING(padID, 22) = '" + stationName + "' AND " +
				"startTime BETWEEN '" + startDate_str + "' AND '" + endDate_str + "' " +
				"ORDER BY startTime";
		try {
	    	sList = db.query(sql);
	        db.commit( );
	     } catch ( Exception e ) {
	    	 e.printStackTrace();
	        db.rollback( );
	     }
		return sList;
	}
	
	public List<PadeLapse> listPadElapseByMaxStartTime(PadLife padlife){
		
		List<PadeLapse> scList = null;
		EntityWrapper<PadeLapse> db = new EntityWrapper<PadeLapse>( );
		
	    try {
	    	String sql = "SELECT pe FROM PadeLapse pe where id.startTime =(SELECT MAX(id.startTime) from PadeLapse where id.padID='" + padlife.getId().getStartLife() + padlife.getId().getStationName()+ "') and id.padID='" + padlife.getId().getStartLife() + padlife.getId().getStationName() + "'";
	    	scList = db.query(sql);
	    	
	        db.commit( );
	     } catch ( Exception e ) {
	    	 log.error("listPadElapseByMaxStartTime",e);
	        db.rollback( );
	     }
		return scList;
	
	}
	
//	4. New SPC
//	SELECT * FROM tapermap;
	public List<TaperMap> listTaperMap(){
		
		List<TaperMap> scList = null;
		EntityWrapper<TaperMap> db = new EntityWrapper<TaperMap>( );
		
	    try {
	    	String sql = "SELECT tm FROM TaperMap tm";
	    	scList = db.query(sql);
	    	
	        db.commit( );
	     } catch ( Exception e ) {
	    	 log.error("listTaperMap",e);
	        db.rollback( );
	     }
		return scList;
	}
	
	public void updateTaperSpc(List<TaperSpc> scList)
	{
		for(TaperSpc scItem : scList)
		{
			EntityWrapper<TaperSpc> db = new EntityWrapper<TaperSpc>( );
			try {
		    	db.merge(scItem);		    	
		        db.commit( );
		     } catch ( Exception e ) {
		    	 log.error("updateTaperSpc",e);
		        db.rollback( );
		     }
		}
	}
	
	public TaperMap getTaperMapByStation(String station){
		
		TaperMap taperMap = null;
		EntityWrapper<TaperMap> db = new EntityWrapper<TaperMap>( );
		
	    try {
	    	String sql = "SELECT tm FROM TaperMap tm WHERE tm.stationName='"+station+"'";
	    	taperMap = db.queryUnique(sql);
	    	
	        db.commit( );
	     } catch ( Exception e ) {
	    	 log.error("getTaperMapByStation",e);
	        db.rollback( );
	     }
		return taperMap;
	}
	
	public TaperMap getTaperMapByTaper(String taperName){
		
		TaperMap taperMap = null;
		EntityWrapper<TaperMap> db = new EntityWrapper<TaperMap>( );
		
	    try {
	    	String sql = "SELECT tm FROM TaperMap tm WHERE tm.leftTaper='"+taperName+"' or tm.rightTaper='"+taperName+"'";
	    	taperMap = db.queryUnique(sql);
	    	
	        db.commit( );
	     } catch ( Exception e ) {
	    	 log.error("getTaperMapByTaper",e);
	        db.rollback( );
	     }
		return taperMap;
	}
	
	public TaperMap getTaperMapByLeftTaper(String taperName){
		
		TaperMap taperMap = null;
		EntityWrapper<TaperMap> db = new EntityWrapper<TaperMap>( );
		
	    try {
	    	String sql = "SELECT tm FROM TaperMap tm WHERE tm.leftTaper='"+taperName+"'";
	    	taperMap = db.queryUnique(sql);
	    	
	        db.commit( );
	     } catch ( Exception e ) {
	    	 log.error("getTaperMapByLeftTaper",e);
	        db.rollback( );
	     }
		return taperMap;
	}
	
	public TaperMap getTaperMapByRightTaper(String taperName){
		
		TaperMap taperMap = null;
		EntityWrapper<TaperMap> db = new EntityWrapper<TaperMap>( );
		
	    try {
	    	String sql = "SELECT tm FROM TaperMap tm WHERE tm.rightTaper='"+taperName+"'";
	    	taperMap = db.queryUnique(sql);
	    	
	        db.commit( );
	     } catch ( Exception e ) {
	    	 log.error("getTaperMapByRightTaper",e);
	        db.rollback( );
	     }
		return taperMap;
	}
	
	public void updateTaperMap(List<TaperMap> scList)
	{
		int updatedCnt = 0;
		for(TaperMap scItem : scList)
		{
			EntityWrapper<TaperMap> db = new EntityWrapper<TaperMap>( );
			try {
		    	db.merge(scItem);		    	
		        db.commit( );
		        updatedCnt++;
		     } catch ( Exception e ) {
		    	 log.error("updateTaperMap",e);
		        db.rollback( );
		     }
		}
	}
	
//	6. Vacuum Value Setting
//	SELECT * FROM tapervalue;
	public List<TaperValue> listTaperValue(){
		
		List<TaperValue> scList = null;
		EntityWrapper<TaperValue> db = new EntityWrapper<TaperValue>( );
		
	    try {
	    	String sql = "SELECT tv FROM TaperValue tv";
	    	scList = db.query(sql);
	    	
	        db.commit( );
	     } catch ( Exception e ) {
	    	 log.error("listTaperValue",e);
	        db.rollback( );
	     }
		return scList;
	}
	
	public TaperValue getTaperValueByTaperId(String taperId){
		
		TaperValue taper = null;
		EntityWrapper<TaperValue> db = new EntityWrapper<TaperValue>( );
		Criterion cri =  Restrictions.eq("taperID", taperId);
		
		try {
			taper = db.queryUnique(new TaperValue(),0,"taperID","asc",cri);
	    	db.commit( );
	     } catch ( Exception e ) {
	    	 log.error("listTaperValueByTaperId",e);
	        db.rollback( );
	     }
		return taper;
	
	}
	
//  6. Vacuum Value Setting
//  UPDATE tapervalue
	public void updateTaperValue(List<TaperValue> scList)
	{
		int updatedCnt = 0;
		for(TaperValue scItem : scList)
		{
			EntityWrapper<TaperValue> db = new EntityWrapper<TaperValue>( );
			try {
		    	db.merge(scItem);		    	
		        db.commit( );
		        updatedCnt++;
		     } catch ( Exception e ) {
		    	 log.error("updateTaperValue",e);
		        db.rollback( );
		     }
		}
	}
	
//	7. Pressure Value Setting
//	SELECT * FROM pressurevalue;
	public List<Pressurevalue> listPressureValue(){
		
		List<Pressurevalue> scList = null;
		EntityWrapper<Pressurevalue> db = new EntityWrapper<Pressurevalue>( );
		
	    try {
	    	String sql = "SELECT pv FROM Pressurevalue pv";
	    	scList = db.query(sql);
	    	
	        db.commit( );
	     } catch ( Exception e ) {
	    	 log.error("listPressureValue",e);
	        db.rollback( );
	     }
		return scList;
	}
	
	public Pressurevalue getPressureById(String taperId){
		
		Pressurevalue taper = null;
		EntityWrapper<Pressurevalue> db = new EntityWrapper<Pressurevalue>( );
		Criterion cri =  Restrictions.eq("pressureID", taperId);
		
		try {
			taper = db.queryUnique(new Pressurevalue(),0,"pressureID","asc",cri);
	    	db.commit( );
	     } catch ( Exception e ) {
	    	 log.error("listTaperValueByTaperId",e);
	        db.rollback( );
	     }
		return taper;
	
	}
	
//	7. Pressure Value Setting
//  UPDATE pressurevalue
	public void updatePressureValue(List<Pressurevalue> scList)
	{
		int updatedCnt = 0;
		for(Pressurevalue scItem : scList)
		{
			EntityWrapper<Pressurevalue> db = new EntityWrapper<Pressurevalue>( );
			try {
		    	db.merge(scItem);		    	
		        db.commit( );
		        updatedCnt++;
		     } catch ( Exception e ) {
		    	 log.error("updatePressureValue",e);
		        db.rollback( );
		     }
		}
	}
		
//9. Activity Log
//	SELECT * FROM activitylog WHERE time BETWEEN �from date� TO �to date�;
	public List<Activitylog> listActivityLog(Date startDate , Date endDate){
		
		List<Activitylog> sList = null;
		EntityWrapper<Activitylog> db = new EntityWrapper<Activitylog>( );
		Criterion cri = Restrictions.between("id.time", new Timestamp(startDate.getTime()), new Timestamp(endDate.getTime()));
System.out.println("From "+new Timestamp(startDate.getTime())+" To "+new Timestamp(endDate.getTime()));
	    try {
	    	sList = db.query(new Activitylog(),0,"id.time","desc",cri);
	    	
	        db.commit( );
	     } catch ( Exception e ) {
	    	 log.error("listActivityLog",e);
	        db.rollback( );
	     }
		return sList;
	}
	
//	10. User ID Manager
//	SELECT * FROM useracccount;
	public List<UserAccount> listUserAccount(){
		
		List<UserAccount> scList = null;
		EntityWrapper<UserAccount> db = new EntityWrapper<UserAccount>( );
		
	    try {
	    	String sql = "SELECT ua FROM UserAccount ua";
	    	scList = db.query(sql);
	    	
	        db.commit( );
	     } catch ( Exception e ) {
	    	 log.error("listUserAccount",e);
	        db.rollback( );
	     }
		return scList;
	}
	
	public boolean updateUserAccount(UserAccount uItem)
	{
		EntityWrapper<UserAccount> db = new EntityWrapper<UserAccount>();
		try
		{
			db.merge(uItem);
			db.commit();
		} catch (Exception e) {
			log.error("updateUserAccount", e);
			db.rollback();
			return false;
		}
		return true;
	}
	
	public boolean deleteUserAccount(UserAccount uItem)
	{
		EntityWrapper<UserAccount> db = new EntityWrapper<UserAccount>();
		try
		{
			db.delete(new UserAccount(), uItem.getUserID());
			db.commit();
		} catch (Exception e) {
			log.error("deleteUserAccount", e);
			db.rollback();
			return false;
		}
		return true;
	}
	
/******************************************************************************/
/****** THE FOLLOWING 5 METHODS ARE USE TO RETRIEVE DATA TO PLOT PARETO *******/
/******************************************************************************/
//11. Pareto
//	SELECT average(value1, value2, value3, value4), dcElement WHERE startdate
//	BETWEEN �from date� AND �to date� AND mountid = �mount id� GROUP BY dcElement;
	public List<Ttv> listTTV(int mountId, Date startDate , Date endDate){
		
		List<Ttv> scList = null;
		EntityWrapper<Ttv> db = new EntityWrapper<Ttv>( );
		Criterion cri =  Restrictions.eq("mountID", mountId);
		Criterion cri2 = Restrictions.between("spcStartDate", new Timestamp(startDate.getTime()), new Timestamp(endDate.getTime()));
	    try {
	    	//String sql = "SELECT ua FROM Ttv ua WHERE spcStartDate BETWEEN "+new Date(startDate.getTime()) +" "+new Date(enddate.getTime()) +" AND mountID="+mountId;
	    	scList = db.query(new Ttv(),0,"spcStartDate","desc",cri,cri2);
	    	
	        db.commit( );
	     } catch ( Exception e ) {
	    	 log.error("listTTV",e);
	        db.rollback( );
	     }
		return scList;
	}
	
	public List<Tir> listTIR(int mountId, Date startDate , Date endDate){
		
		List<Tir> scList = null;
		EntityWrapper<Tir> db = new EntityWrapper<Tir>( );
		Criterion cri =  Restrictions.eq("mountID", mountId);
		Criterion cri2 = Restrictions.between("spcStartDate", new Timestamp(startDate.getTime()), new Timestamp(endDate.getTime()));
	    try {
	    	//String sql = "SELECT ua FROM Tir ua WHERE spcStartDate BETWEEN "+new Date(startDate.getTime()) +" "+new Date(enddate.getTime()) +" AND mountID="+mountId;
	    	scList = db.query(new Tir(),0,"spcStartDate","desc",cri,cri2);
	    	
	        db.commit( );
	     } catch ( Exception e ) {
	        db.rollback( );
	     }
		return scList;
	}
	
	public List<Stir> listSTIR(int mountId, Date startDate , Date endDate){
		
		List<Stir> scList = null;
		EntityWrapper<Stir> db = new EntityWrapper<Stir>( );
		Criterion cri =  Restrictions.eq("mountID", mountId);
		Criterion cri2 = Restrictions.between("spcStartDate", new Timestamp(startDate.getTime()), new Timestamp(endDate.getTime()));
	    try {
	    	//String sql = "SELECT ua FROM Tir ua WHERE spcStartDate BETWEEN "+new Date(startDate.getTime()) +" "+new Date(enddate.getTime()) +" AND mountID="+mountId;
	    	scList = db.query(new Stir(),0,"spcStartDate","desc",cri,cri2);
	    	
	        db.commit( );
	     } catch ( Exception e ) {
	        db.rollback( );
	     }
		return scList;
	}
	
	public List<TaperSpc> listTaperSPC(int mountId, Date startDate , Date endDate){
		
		List<TaperSpc> scList = null;
		EntityWrapper<TaperSpc> db = new EntityWrapper<TaperSpc>( );
		Criterion cri =  Restrictions.eq("mountID", mountId);
		Criterion cri2 = Restrictions.between("spcStartDate", new Timestamp(startDate.getTime()), new Timestamp(endDate.getTime()));
	    try {
	    	//String sql = "SELECT ua FROM Tir ua WHERE spcStartDate BETWEEN "+new Date(startDate.getTime()) +" "+new Date(enddate.getTime()) +" AND mountID="+mountId;
	    	scList = db.query(new TaperSpc(),0,"spcStartDate","desc",cri,cri2);
	    	
	        db.commit( );
	     } catch ( Exception e ) {
	        db.rollback( );
	     }
		return scList;
	}
	
	public List<Removal> listRemoval(int mountId, Date startDate , Date endDate){
		
		List<Removal> scList = null;
		EntityWrapper<Removal> db = new EntityWrapper<Removal>( );
		Criterion cri =  Restrictions.eq("mountID", mountId);
		Criterion cri2 = Restrictions.between("spcStartDate", new Timestamp(startDate.getTime()), new Timestamp(endDate.getTime()));
	    try {
	    	//String sql = "SELECT ua FROM Tir ua WHERE spcStartDate BETWEEN "+new Date(startDate.getTime()) +" "+new Date(enddate.getTime()) +" AND mountID="+mountId;
	    	scList = db.query(new Removal(),0,"spcStartDate","desc",cri,cri2);
	    	
	        db.commit( );
	     } catch ( Exception e ) {
	        db.rollback( );
	     }
		return scList;
	}
	
/******************************************************************************/
/**** THE FOLLOWING 5 METHODS ARE USE TO RETRIEVE DATA TO PLOT TEND CHART *****/
/******************************************************************************/

	/**
	 * Retrieve TTV data from database, matching with the dcElement, and have
	 * SPC start date between starting date and end date.
	 * @param dcElement The SPC name mapped to the Taper Arm to be query
	 * @param startDate The starting date time of the data
	 * @param endDate The ending date time of the data
	 * @return List of TTV object which matches with the condition
	 */
	public List<Ttv> listTTVByDCElement(String dcElement , Date startDate , Date endDate){
		
		List<Ttv> scList = null;
		EntityWrapper<Ttv> db = new EntityWrapper<Ttv>( );
		Criterion cri =  Restrictions.eq("id.dcElement", dcElement);
		Criterion cri2 = Restrictions.between("spcStartDate", new Timestamp(startDate.getTime()), new Timestamp(endDate.getTime()));
	    try {
	    	//String sql = "SELECT ua FROM Ttv ua WHERE spcStartDate BETWEEN "+new Date(startDate.getTime()) +" "+new Date(enddate.getTime()) +" AND mountID="+mountId;
	    	scList = db.query(new Ttv(),0,"spcStartDate","desc",cri,cri2);
	    	
	        db.commit( );
	     } catch ( Exception e ) {
	    	 log.error("listTTVByDCElement",e);
	        db.rollback( );
	     }
		return scList;
	}

	/**
	 * Retrieve TIR data from database, matching with the dcElement, and have
	 * SPC start date between starting date and end date.
	 * @param dcElement The SPC name mapped to the Taper Arm to be query
	 * @param startDate The starting date time of the data
	 * @param endDate The ending date time of the data
	 * @return List of TIR object which matches with the condition
	 */
	public List<Tir> listTIRByDCElement(String dcElement , Date startDate , Date endDate){
		
		List<Tir> scList = null;
		EntityWrapper<Tir> db = new EntityWrapper<Tir>( );
		Criterion cri =  Restrictions.eq("id.dcElement", dcElement);
		System.out.println(new Timestamp(startDate.getTime()).toString());
		System.out.println(new Timestamp(endDate.getTime()).toString());
		Criterion cri2 = Restrictions.between("spcStartDate", new Timestamp(startDate.getTime()), new Timestamp(endDate.getTime()));
	    try {
	    	//String sql = "SELECT ua FROM Tir ua WHERE spcStartDate BETWEEN "+new Date(startDate.getTime()) +" "+new Date(enddate.getTime()) +" AND mountID="+mountId;
	    	scList = db.query(new Tir(),0,"spcStartDate","desc",cri,cri2);
	    	
	        db.commit( );
	     } catch ( Exception e ) {
	        db.rollback( );
	     }
		return scList;
	}
	
	/**
	 * Retrieve STIR data from database, matching with the dcElement, and have
	 * SPC start date between starting date and end date.
	 * @param dcElement The SPC name mapped to the Taper Arm to be query
	 * @param startDate The starting date time of the data
	 * @param endDate The ending date time of the data
	 * @return List of STIR object which matches with the condition
	 */
	public List<Stir> listSTIRByDCElement(String dcElement , Date startDate , Date endDate){
		
		List<Stir> scList = null;
		EntityWrapper<Stir> db = new EntityWrapper<Stir>( );
		Criterion cri =  Restrictions.eq("id.dcElement", dcElement);
		System.out.println(new Timestamp(startDate.getTime()).toString());
		System.out.println(new Timestamp(endDate.getTime()).toString());
		Criterion cri2 = Restrictions.between("spcStartDate", new Timestamp(startDate.getTime()), new Timestamp(endDate.getTime()));
	    try {
	    	//String sql = "SELECT ua FROM stir ua WHERE spcStartDate BETWEEN "+new Date(startDate.getTime()) +" "+new Date(enddate.getTime()) +" AND mountID="+mountId;
	    	scList = db.query(new Stir(),0,"spcStartDate","desc",cri,cri2);
	    	
	        db.commit( );
	     } catch ( Exception e ) {
	        db.rollback( );
	     }
		return scList;
	}
	
	/**
	 * Retrieve Removal data from database, matching with the dcElement, and have
	 * SPC start date between starting date and end date.
	 * @param dcElement The SPC name mapped to the Taper Arm to be query
	 * @param startDate The starting date time of the data
	 * @param endDate The ending date time of the data
	 * @return List of Removal object which matches with the condition
	 */
	public List<Removal> listRemovalByDCElement(String dcElement , Date startDate , Date endDate){
		
		List<Removal> scList = null;
		EntityWrapper<Removal> db = new EntityWrapper<Removal>( );
		Criterion cri =  Restrictions.eq("id.dcElement", dcElement);
		System.out.println(new Timestamp(startDate.getTime()).toString());
		System.out.println(new Timestamp(endDate.getTime()).toString());
		Criterion cri2 = Restrictions.between("spcStartDate", new Timestamp(startDate.getTime()), new Timestamp(endDate.getTime()));
	    try {
	    	//String sql = "SELECT ua FROM stir ua WHERE spcStartDate BETWEEN "+new Date(startDate.getTime()) +" "+new Date(enddate.getTime()) +" AND mountID="+mountId;
	    	scList = db.query(new Removal(),0,"spcStartDate","desc",cri,cri2);
	    	
	        db.commit( );
	     } catch ( Exception e ) {
	        db.rollback( );
	     }
		return scList;
	}
	
	/**
	 * Retrieve Taper SPC data from database, matching with the dcElement, and have
	 * SPC start date between starting date and end date.
	 * @param dcElement The SPC name mapped to the Taper Arm to be query
	 * @param startDate The starting date time of the data
	 * @param endDate The ending date time of the data
	 * @return List of TaperSpc object which matches with the condition
	 */
	public List<TaperSpc> listTaperSpcByDCElement(String dcElement , Date startDate , Date endDate){
		
		List<TaperSpc> scList = null;
		EntityWrapper<TaperSpc> db = new EntityWrapper<TaperSpc>( );
		Criterion cri =  Restrictions.eq("id.dcElement", dcElement);
		System.out.println(new Timestamp(startDate.getTime()).toString());
		System.out.println(new Timestamp(endDate.getTime()).toString());
		Criterion cri2 = Restrictions.between("spcStartDate", new Timestamp(startDate.getTime()), new Timestamp(endDate.getTime()));
	    try {
	    	//String sql = "SELECT ua FROM stir ua WHERE spcStartDate BETWEEN "+new Date(startDate.getTime()) +" "+new Date(enddate.getTime()) +" AND mountID="+mountId;
	    	scList = db.query(new TaperSpc(),0,"spcStartDate","desc",cri,cri2);
	    	
	        db.commit( );
	     } catch ( Exception e ) {
	        db.rollback( );
	     }
		return scList;
	}
/******************************************************************************/
/********************* END OF QUERY DATA FOR TEND CHART ***********************/
/******************************************************************************/

	public List<TaperSpc> listTaperSpcBylotIDdcElement(String lotID,String dcElementTP){
		
		List<TaperSpc> scList = null;
		EntityWrapper<TaperSpc> db = new EntityWrapper<TaperSpc>( );
		Criterion cri =  Restrictions.eq("id.lotID", lotID);
		Criterion cri2 = Restrictions.eq("id.dcElement", dcElementTP);
	    try {
	    	//String sql = "SELECT ua FROM Ttv ua WHERE spcStartDate BETWEEN "+new Date(startDate.getTime()) +" "+new Date(enddate.getTime()) +" AND mountID="+mountId;
	    	scList = db.query(new TaperSpc(),0,"spcStartDate","asc",cri,cri2);
	    	
	        db.commit( );
	     } catch ( Exception e ) {
	    	 log.error("listTaperSpcBylotIDdcElement",e);
	        db.rollback( );
	     }
		return scList;
	
	}
	
	public Coefficient listCoefficientByStationArm(String stationName,String arm){
		
		Coefficient coe = null;
		EntityWrapper<Coefficient> db = new EntityWrapper<Coefficient>( );
		
	    try {
	    	String sql = "SELECT co FROM Coefficient co WHERE co.id.stationName='"+stationName+"' AND co.id.arm='"+arm+"'";
	    	coe = db.queryUnique(sql);
	    	
	        db.commit( );
	     } catch ( Exception e ) {
	    	 log.error("listCoefficientByStationArm",e);
	        db.rollback( );
	     }
		return coe;
	
	}
	
	public boolean updateCoefficient(Coefficient coItem)
	{
		EntityWrapper<Coefficient> db = new EntityWrapper<Coefficient>();

	    try {
	    	db.merge(coItem);
	        db.commit( );
	    } catch ( Exception e ) {
	    	e.printStackTrace();
	    	log.error("updateCoefficient",e);
	        db.rollback( );
	        return false;
	    }
	    
	    return true;
	}
	
	public Padlot listPadLotByStation(String stationName){
		
		Padlot padlot = null;
		EntityWrapper<Padlot> db = new EntityWrapper<Padlot>( );
		
	    try {
	    	String sql = "SELECT pl FROM Padlot pl WHERE pl.id.stationName='"+stationName+"'";
	    	padlot = db.queryUnique(sql);
	    	
	        db.commit( );
	     } catch ( Exception e ) {
	    	 log.error("listPadLotByStation",e);
	        db.rollback( );
	     }
		return padlot;
	
	}
	
	public Polynomial listPolyByArmPadlot(String armId , String padLotId){
		
		Polynomial padlot = null;
		EntityWrapper<Polynomial> db = new EntityWrapper<Polynomial>( );
		
	    try {
	    	String sql = "SELECT poly FROM Polynomial poly WHERE poly.id.armID='"+armId+"' AND poly.id.padLotID='"+padLotId+"'";
	    	padlot = db.queryUnique(sql);
	    	
	        db.commit( );
	     } catch ( Exception e ) {
	    	 log.error("listPolyByArmPadlot",e);
	        db.rollback( );
	     }
		return padlot;
	
	}

	public boolean updatePolynomial(Polynomial pItem)
	{
		EntityWrapper<Polynomial> db = new EntityWrapper<Polynomial>();
		try {
			db.merge(pItem);
			db.commit();
		}
		catch ( Exception e ) {
			log.error("updatePadLot",e);
	        db.rollback( );
	        return false;
	    }
		
		return true;
	}
/******************************************************************************/
/****************** Query padbatch to retrieve padBatchID *********************/
/******************************************************************************/
	
	public List<Padbatch> getPadBatchID()
	{
		List<Padbatch> scList = null;
		EntityWrapper<Padbatch> db = new EntityWrapper<Padbatch>();
	    try {
	    	scList = db.query(new Padbatch());	    	
	        db.commit( );	        
	    } catch ( Exception e ) {
	    	log.error("getPadBatchID",e);
	        db.rollback( );
	    }
		return scList;
	}
	
	public void addPadBatchID(String padBatchID)
	{
		EntityWrapper<Padbatch> db = new EntityWrapper<Padbatch>();
		try
		{
			Padbatch pItem = new Padbatch();
			pItem.setPadBatchID(padBatchID);
			db.merge(pItem);
			db.commit();
		}
		catch(Exception e)
		{
			log.error("addPadBatchID", e);
			db.rollback();
		}
	}
	
	public void deletePadBatchID(String[] padBatchIDArray)
	{
		if(padBatchIDArray.length <= 0)
			return;
		
		EntityWrapper<Padbatch> db = new EntityWrapper<Padbatch>();
		try
		{
			for(int i=0; i<padBatchIDArray.length; i++)
			{
				db.delete(new Padbatch(), padBatchIDArray[i]);
				System.out.println(padBatchIDArray[i] +"");
			}
			db.commit();
		}
		catch(Exception e)
		{
			System.out.println(e);
			log.error("deletePadBatchID", e);
			db.rollback();
		}
	}

	public Integer getCountActiveStationByPadLotID(String padLotID)
	{
		Integer padCount=0;
		EntityWrapper<Long> db = new EntityWrapper<Long>( );
		
	    try {
	    	String sql = "SELECT count(stationName) FROM Padlot pl WHERE pl.id.padLotId='"+padLotID+"' AND pl.active='1'";
	    	padCount = ((Long)db.queryUnique(sql)).intValue();	    	
	        db.commit( );
	    } catch ( Exception e ) {
	    	e.printStackTrace();
	    	log.error("getCurrentPadCount",e);
	        db.rollback( );
	    }
	    if(padCount == null)
	    	padCount = 0;
		return padCount;
	}
	
	public Padlot getActivePadlotByStationName(String stationName)
	{
		List<Padlot> padLotItem = null;
		EntityWrapper<Padlot> db = new EntityWrapper<Padlot>();
		try {
			padLotItem = db.query("SELECT pl FROM Padlot pl WHERE pl.id.stationName='"+stationName+"' AND pl.active='1'");
		} catch ( Exception e ) {
	    	e.printStackTrace();
	    	log.error("getActivePadlotByStationName",e);
	        db.rollback( );
	    }
		if(padLotItem == null || padLotItem.size()==0)
			return null;
		
		return padLotItem.get(0);
	}
	
	public Padlot getPadLotByStationPadLot(String padLotID, String stationName)
	{
		Padlot padLot =null;
		EntityWrapper<Padlot> db = new EntityWrapper<Padlot>( );
		
	    try {
	    	String sql = "SELECT pl FROM Padlot pl WHERE pl.id.padLotId='"+padLotID+"' AND pl.id.stationName='"+stationName+"'";
	    	padLot = db.queryUnique(sql);	    	
	        db.commit( );
	    } catch ( Exception e ) {
	    	e.printStackTrace();
	    	log.error("getPadLotByStationPadLot",e);
	        db.rollback( );
	    }

		return padLot;
	}
	
	public Integer getCurrentPadCount(String padLotId, String stationName)
	{
		Integer padCount=0;
		EntityWrapper<Integer> db = new EntityWrapper<Integer>( );
		
	    try {
	    	String sql = "SELECT max(padCount) FROM Padlot pl WHERE pl.id.padLotId='"+padLotId+"' AND pl.id.stationName='"+stationName+"'";
	    	padCount = db.queryUnique(sql);	    	
	        db.commit( );
	    } catch ( Exception e ) {
	    	e.printStackTrace();
	    	log.error("getCurrentPadCount",e);
	        db.rollback( );
	    }
	    if(padCount == null)
	    	padCount = 0;
		return padCount;
	}
	
	public void updatePadLot(Padlot pItem)
	{
		EntityWrapper<Padlot> db = new EntityWrapper<Padlot>();
		List<Padlot> scList = null;		

		String stationName = pItem.getId().getStationName();
		
		try { 	
			// Set other padLot as inactive
			scList = db.query("SELECT pl FROM Padlot pl WHERE pl.id.stationName= '"+stationName+"'");
			for(Padlot pItem2 : scList)
			{
				pItem2.setActive(0);
				db.merge(pItem2);
	    		db.commit();
				db = new EntityWrapper<Padlot>();
	    	}	        
		} catch ( Exception e ) {
			e.printStackTrace();
			log.error("updatePadLot",e);
	        db.rollback( );
	    }
		   	
	    db = new EntityWrapper<Padlot>();
	    try { 	
			db.merge(pItem);
	        db.commit( );
	        
	     } catch ( Exception e ) {
			e.printStackTrace();
			log.error("updatePadLot",e);
	        db.rollback( );
	     }
	}

	
	
	/******************************************************************************/
	/**************************** Query polyresult  *******************************/
	/******************************************************************************/
	public List<PolyResult> listPolyResultByStationPadID(String stationId, String armId, String padLotId){
		
		List<PolyResult> pList = null;
		EntityWrapper<PolyResult> db = new EntityWrapper<PolyResult>( );
		Criterion cri =  Restrictions.eq("id.station", stationId);
		Criterion cri2 = Restrictions.eq("id.arm", armId);
		Criterion cri3 = Restrictions.eq("id.padlotid", padLotId);
	    try {
	    	pList = db.query(new PolyResult(),0,"id.padlife","asc",cri,cri2,cri3);
	    	
	        db.commit( );
	     } catch ( Exception e ) {
	    	 log.error("listPolyResultByStationPadID",e);
	        db.rollback( );
	     }
		return pList;
	
	}
}
